package com.ajou.adonate.database;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import com.ajou.adonate.imap.*;

import org.json.simple.*;

public class DatabaseManager {
	private String dburl = "";
	private String dbuser = "";
	private String dbpass = "";
	private String jdbcDriver = "";
	
	public DatabaseManager() {
		dburl = "jdbc:mysql://211.189.127.75:3306/adonate?useUnicode=true&amp;characterEncoding=UTF-8";
		dbuser = "root";
		dbpass = "apmsetup";
		jdbcDriver = "com.mysql.jdbc.Driver";
	}
	
	public void setDbUrl(String url) {
		dburl = url;
	}
	
	public String getDbUrl() {
		return dburl;
	}
	
	public void setDbUser(String user) {
		dbuser = user;
	}
	
	public String getDbUser() {
		return dbuser;
	}
	
	public void setDbPass(String pass) {
		dbpass = pass;
	}
	
	public String getDbPass () {
		return dbpass;
	}
	
	public void setJdbcDriver (String driver) {
		jdbcDriver = driver;
	}
	
	public String getJdbcDriver () {
		return jdbcDriver;
	}
	/**
	 * Function for opening connection to the database
	 */
	protected Connection getConnection() {
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(getDbUrl(),getDbUser(),getDbPass());			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * Function to close open connection to database
	 */
	protected void closeConnection(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	protected void closeResource(JSONObject resource) {
		ResultSet rs = (ResultSet)resource.get("rs");
		PreparedStatement ps = (PreparedStatement)resource.get("ps");
		Connection conn = (Connection)resource.get("conn");
		
		try {
			rs.close();
			ps.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		closeConnection(conn);
	}
	
	@SuppressWarnings("unchecked")
	protected JSONObject executeQuery(String sqlQuery) {
		Connection conn = getConnection();
		if(conn != null) {
			ResultSet rs = null;
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(sqlQuery);
				rs = ps.executeQuery();
				
				JSONObject result = new JSONObject();
				result.put("rs", rs);
				result.put("ps", ps);
				result.put("conn", conn);
				
				return result;
			} catch(SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	protected boolean executeUpdate(String sqlQuery)	{
		Connection conn = getConnection();
		if(conn != null) {
			Statement st = null;
			try {
				st = conn.createStatement();
				st.executeUpdate(sqlQuery);
			} catch(SQLException e) {
				e.printStackTrace();
				return false;
			}
			finally {
				try {
					st.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
				closeConnection(conn);	
			}
		}
		return true;
	}

	public int getAccountId(String email_account) {
		int account_id = 0;
		
		String sqlQuery = 
			"SELECT account_id " +
			"FROM account " +
			"WHERE email_account='" + email_account + "'";
		JSONObject resource = executeQuery(sqlQuery);
		
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			if (rs.next())
				account_id = rs.getInt("account_id");
			else
				account_id = -1;
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}		
		closeResource(resource);		
		
		return account_id;
	}
	
	public int getMemberId(String email_account) {
		int member_id = 0;
		
		String sqlQuery = 
			"SELECT member_id " +
			"FROM member " +
			"WHERE email_account='" + email_account + "'";
		JSONObject resource = executeQuery(sqlQuery);
		
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			if (rs.next())
				member_id = rs.getInt("member_id");
			else
				member_id = -1;
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}		
		closeResource(resource);		
		
		return member_id;
	}
	
	public boolean insertAccount(String email_account, String oauth_token, String oauth_token_secret) {
		String sqlQuery = "INSERT INTO account (account_id, email_account, oauth_token, oauth_token_secret) VALUES(NULL,'"+email_account+"','"+oauth_token+"','"+oauth_token_secret+"')";
		System.out.println(sqlQuery);
		return executeUpdate(sqlQuery);
	}
	
	public boolean updateAccount(String email_account, String oauth_token, String oauth_token_secret, int account_id) {
		String sqlQuery = "UPDATE account SET oauth_token='"+oauth_token+"', oauth_token_secret='"+oauth_token_secret+"' WHERE account_id=" + account_id;
		System.out.println(sqlQuery);
		return executeUpdate(sqlQuery);
	}
	
	public HashMap getFile(int file_id) {
		HashMap hs = new HashMap();
		
		String sqlQuery = 
			"SELECT * " +
			"FROM file " +
			"WHERE file_id=" + file_id;
		JSONObject resource = executeQuery(sqlQuery);
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			while (rs.next()) {
				hs.put("file_id", rs.getInt("file_id"));
				hs.put("file_name", rs.getString("name"));
				hs.put("file_size", rs.getString("file_size"));
				hs.put("file_format", rs.getString("file_format"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		closeResource(resource);		
		
		return hs;
	}
	
	public String getHashByMovieId(String movie_id) {
		String hashCode;
		
		String sqlQuery = 
			"SELECT filehash " +
			"FROM movie " +
			"WHERE movie_id=" + movie_id;
		JSONObject resource = executeQuery(sqlQuery);
		
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			if (rs.next())
				hashCode = rs.getString("filehash");
			else
				hashCode = "null";
		} catch (SQLException e) {
			e.printStackTrace();
			hashCode = "null";
		}		
		closeResource(resource);		
		
		return hashCode;
	}
	
	public String getEmailByMovieId(String movie_id) {
		String ret;
		
		String sqlQuery = "SELECT a.email_account FROM movie as m, account as a where m.movie_id=" + Integer.parseInt(movie_id) + " and m.account_id = a.account_id";
		JSONObject resource = executeQuery(sqlQuery);
		
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			if (rs.next())
				ret = rs.getString("email_account");
			else
				ret = "null";
		} catch (SQLException e) {
			e.printStackTrace();
			ret = "null";
		}		
		closeResource(resource);		
		
		return ret;
	}
	
	public String getOAuthTokenByMovieId(String movie_id) {
		String ret;
		
		String sqlQuery = "SELECT a.oauth_token FROM movie as m, account as a where m.movie_id=" + Integer.parseInt(movie_id) + " and m.account_id = a.account_id";
		JSONObject resource = executeQuery(sqlQuery);
		
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			if (rs.next())
				ret = rs.getString("oauth_token");
			else
				ret = "null";
		} catch (SQLException e) {
			e.printStackTrace();
			ret = "null";
		}		
		closeResource(resource);		
		
		return ret;
	}
	
	public String getOAuthTokenSecretByMovieId(String movie_id) {
		String ret;
		
		String sqlQuery = "SELECT a.oauth_token_secret FROM movie as m, account as a where m.movie_id=" + Integer.parseInt(movie_id) + " and m.account_id = a.account_id";
		JSONObject resource = executeQuery(sqlQuery);
		
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			if (rs.next())
				ret = rs.getString("oauth_token_secret");
			else
				ret = "null";
		} catch (SQLException e) {
			e.printStackTrace();
			ret = "null";
		}		
		closeResource(resource);		
		
		return ret;
	}
	
	public int sumUserFileSize(int member_id) {
		String sqlQuery = 
			"SELECT file_size " +
			"FROM file " +
			"WHERE member_id=" + member_id;
		JSONObject resource = executeQuery(sqlQuery);
		
		ResultSet rs = (ResultSet)resource.get("rs");
		
		int total_size = 0;
		try {
			while (rs.next())
				total_size += Integer.parseInt(rs.getString("file_size"));
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		closeResource(resource);		
		
		return total_size;		
	}
	
	@SuppressWarnings("unchecked")
	public JSONArray getFileList(int member_id, int depth, int parent_id) {
		JSONObject result = new JSONObject();
		
		JSONArray directories = new JSONArray();
		JSONArray files = new JSONArray();
		JSONObject resource = null;
		ResultSet rs = null;
		String sqlQuery = null;

		try {
			//// get file list
			sqlQuery = 
				"SELECT file_id, name, file_size, file_format, file_desc " + 
				"FROM file " + 
				"WHERE member_id=" + member_id + " AND parent_id=" + parent_id;
			resource = executeQuery(sqlQuery);
			
			System.out.println(sqlQuery);
			
			rs = (ResultSet)resource.get("rs");
			while (rs.next()) {
				JSONObject file = new JSONObject();
				file.put("file_id", rs.getString("file_id"));
				file.put("name", rs.getString("name")); 		
				file.put("file_size", rs.getString("file_size"));
				file.put("file_format", rs.getString("file_format"));	
				file.put("file_desc", rs.getString("file_desc"));
				files.add(file);
			}
			closeResource(resource);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
		return files;
	}
	
	@SuppressWarnings("unchecked")
	public JSONArray getDirectoryList(int member_id, int depth, int parent_id) {
		JSONObject result = new JSONObject();
		
		JSONArray directories = new JSONArray();
		JSONArray files = new JSONArray();

		try {
			//// get directory list
			String sqlQuery = 
				"SELECT directory_id, name " + 
				"FROM directory " + 
				"WHERE member_id=" + member_id + " AND parent_id=" + parent_id;
			JSONObject resource = executeQuery(sqlQuery);
			
			ResultSet rs = (ResultSet)resource.get("rs");
			while (rs.next()) {
				JSONObject directory = new JSONObject();
				directory.put("text", rs.getString("name"));
				directory.put("id", rs.getString("directory_id"));
				directory.put("hasChildren", true);
				
				directories.add(directory);
			}
			closeResource(resource);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
		return directories;
	}
	
	public boolean updatePassword(int member_id, String password) {
		String sqlQuery = 
			"UPDATE member " +
			"SET password='" + password + "' " +
			"WHERE member_id=" + member_id;
		return executeUpdate(sqlQuery);
	}

	public HashMap getAllAccounts() {
		HashMap hs = new HashMap();
		
		String sqlQuery = "SELECT * FROM account";
		JSONObject resource = executeQuery(sqlQuery);
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			while (rs.next()) {
				hs.put(rs.getInt("account_id"), 0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		closeResource(resource);		
		
		return hs;
	}
	
	public int getEmptyAccountId() {
		int account_id = 0;
		int temp = 0;
		int min = 9999999;
		HashMap hs = new HashMap();
		
		hs = this.getAllAccounts();
		
		String sqlQuery = "SELECT * from movie";
		JSONObject resource = executeQuery(sqlQuery);
		
		ResultSet rs = (ResultSet)resource.get("rs");
		
		try {
			if (rs.next()) {
				account_id = rs.getInt("account_id");
				temp = (Integer)hs.get(account_id);
				hs.put(account_id, temp++);
			} else {
				account_id = 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 1;
		}		
		closeResource(resource);		
		
		//Hash를 돌면서 가장작은값을 리턴
		Set key = hs.keySet();
		int keyName, value;
		  
		for(Iterator iterator = key.iterator(); iterator.hasNext();) {
			keyName = (Integer) iterator.next();
			value = (Integer) hs.get(keyName);
			if(value <= min) {
				account_id = keyName;
				min = value;
			}
//			System.out.println(keyName +" = " +valueName);
		}
		  
		return account_id;
	}
	
	public boolean uploadComplete(String email_account, String title, String author, String description, long filehash, long filesize) {
		boolean ret = false;
		
		int memberId = this.getMemberId(email_account);
		int accountId = this.getEmptyAccountId();
		
		System.out.println("member id : " + memberId);
		System.out.println("account id : " + accountId);
		
		if(memberId > -1) {
			ArrayList<String> data = new ArrayList<String>();
			data.add("'" + title + "'");
			data.add("'" + author + "'");
			data.add("'" + description + "'");
			data.add("'" + Long.toString(filehash) + "'");
			data.add("'" + Long.toString(filesize) + "'");
			data.add("'" + this.getDate() + "'");
			data.add("" + memberId);
			data.add("" + accountId);
			
			return putUserData("movie(title, author, description, filehash, filesize, upload_date, member_id, account_id)", data);
		}
		
		return ret;
	}
	
	public boolean refreshFileList(int member_id) {
		String sqlQuery = "DELETE FROM file " +
							"WHERE member_id = " + member_id;
		
		return executeUpdate(sqlQuery);
	}
	
	public boolean clearFileCache() {
		String sqlQuery = "UPDATE file SET is_cache = 0";

		return executeUpdate(sqlQuery);
	}
	
	public boolean isCaching(int file_id) {
		boolean isCaching = false;
		
		try {
			String sqlQuery = "SELECT is_caching FROM file " +
					"WHERE file_id = " + file_id;
			JSONObject resource = executeQuery(sqlQuery);
			
			ResultSet rs = (ResultSet)resource.get("rs");
			rs.next();
			if (rs.getInt("is_caching") == 1)
				isCaching = true;

			closeResource(resource);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return isCaching;
	}
	
	public boolean putUserData(String table, ArrayList<String> data) {
		String sqlQuery = "INSERT INTO " + table + " VALUES(";
				
		for (String attr : data)
			sqlQuery += attr + ",";

		sqlQuery = sqlQuery.substring(0, sqlQuery.length() - 1);
		sqlQuery += ")";
		System.out.print(sqlQuery);
		return executeUpdate(sqlQuery);
	}
	
	public String getDate() {
		Date now = new Date();
		Calendar currentDate = Calendar.getInstance();
		DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss"); //yyyy-MM-dd HH:mm:ss
		
		return df.format(now).toString();
	}
}
